package com.eorchis.module.sysdeptuser.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.module.sysdeptuser.dao.ISysDeptUserDao;
import com.eorchis.module.sysdeptuser.domain.SysDepartmentBean;
import com.eorchis.module.sysdeptuser.domain.SysDepartmentUserBean;
import com.eorchis.module.sysdeptuser.domain.SysUserBean;
import com.eorchis.module.sysdeptuser.ui.commond.SysDeptUserQueryCommond;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: SysDeptUserDaoImpl<br>
 * Description: 同步部门人员数据接口实现<br>
 * Copyright @ 2011~2016 Goldgov .All rights reserved.<br>
 * @author LongJZ
 * @createDate 2016-1-7
 * @version $Revision: $
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.sysdeptuser.dao.impl.SysDeptUserDaoImpl")
public class SysDeptUserDaoImpl extends HibernateAbstractBaseDao implements ISysDeptUserDao {

	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	/**
	 * 返回人员表查询字段(UUM)
	 * @return
	 */
	public String UserQueryStr() {
		return "u.USERID, u.LOGIN_ID, u.PASSWORD, u.USER_NAME, u.SEX_CODE, u.BIRTHDAY, u.PHONE, " +
				"u.MOBILE_TELEPHONE, u.EMAIL, u.FOLK_CODE, u.PAPER_TYPE_CODE, u.PAPER_NUM, u.POLITICES_CODE, " +
				"u.DEGREE_CODE, u.DUTY_LEVEL_CODE, u.DUTY_REMARK, u.ACTIVE_STATE, u.VIEW_STATE, u.GUID";
	}

	/**
	 * 返回部门表查询字段(UUM)
	 * @return
	 */
	public String DepartmentQueryStr() {
		return "d.DEPID, d.PARENT_ID, d.DEPNAME, d.DEPCODE, d.DEPT_ADDRESS, d.DEPT_POST_NUM, d.DEPT_PHONE, " +
				"d.ORDER_NUM, d.DEPKIND_CODE, d.TREEPATH, d.ACTIVE_STATE, d.REPORT_DEPID, d.LAST_OPERATE_TIME";
	}

	/**
	 * 返回部门人员关系表查询字段(UUM)
	 * @return
	 */
	public String DepartmentUserQueryStr() {
		return "du.DEPUSER_ID, du.USERID, du.DEPID, du.OLD_DEPTID, du.ACTIVE_STATE, du.ORDER_NUM, du.LAST_OPERATE_TIME";
	}

	/**
	 * 同步当前管理范围下的部门人员及关联关系
	 * @param queryCommond
	 * @return
	 * @author LongJZ
	 * @date 2016-1-7
	 */
	public Boolean sysDeptUser(final SysDeptUserQueryCommond queryCommond) throws Exception {
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>() {
			@Override
			public Boolean doInConnection(Connection con) throws SQLException, DataAccessException {
				PreparedStatement pstmt = null;
				try {
					String querySql = " from uum_department_user@UUM_LINK du" +
							" right join uum_user@UUM_LINK u on du.userid = u.userid" +
							" right join uum_department@UUM_LINK d on du.depid = d.depid" +
							" where d.treepath like ?";
					String sql = "";
					if (!PropertyUtil.objectNotEmpty(queryCommond.getQueryType()))
						return false;
					else if (queryCommond.getQueryType().equals(SysUserBean.QUERY_TYPE)) {
						sql = "merge into base_user ou using (select " + UserQueryStr() + querySql + 
								" group by " + UserQueryStr() + ") su on (ou.userid = su.userid)" +
								" when matched then update set" +
								" ou.login_id = su.login_id," +
								" ou.password = su.password," +
								" ou.user_name = su.user_name," +
								" ou.sex_code = su.sex_code," +
								" ou.birthday = su.birthday," +
								" ou.phone = su.phone," +
								" ou.mobile_telephone = su.mobile_telephone," +
								" ou.email = su.email," +
								" ou.folk_code = su.folk_code," +
								" ou.paper_type_code = su.paper_type_code," +
								" ou.paper_num = su.paper_num," +
								" ou.politices_code = su.politices_code," +
								" ou.degree_code = su.degree_code," +
								" ou.duty_level_code = su.duty_level_code," +
								" ou.duty_remark = su.duty_remark," +
								" ou.active_state = su.active_state," +
								" ou.view_state = su.view_state," +
								" ou.guid = su.guid" +
								" when not matched then insert " +
								"(" + UserQueryStr().replaceAll("u.", "ou.") + ")" +
								" values " +
								"(" + UserQueryStr().replaceAll("u.", "su.") + ")";
					} else if (queryCommond.getQueryType().equals(SysDepartmentBean.QUERY_TYPE)) {
						sql = "merge into base_department od using (select " + DepartmentQueryStr() + querySql +
								" group by " + DepartmentQueryStr() + ") sd on (od.depid = sd.depid)" +
								" when matched then update set" +
								" od.parent_id = sd.parent_id," +
								" od.depname = sd.depname," +
								" od.dept_code = sd.depcode," +
								" od.dept_address = sd.dept_address," +
								" od.dept_post_num = sd.dept_post_num," +
								" od.dept_phone = sd.dept_phone," +
								" od.order_num = sd.order_num," +
								" od.depkind_code = sd.depkind_code," +
								" od.treepath = sd.treepath," +
								" od.active_state = sd.active_state," +
								" od.report_depid = sd.report_depid," +
								" od.operate_time = sd.last_operate_time" +
								" when not matched then insert " +
								"(" + DepartmentQueryStr().replaceAll("d.", "od.").replace("DEPCODE", "DEPT_CODE").replace("LAST_OPERATE_TIME", "OPERATE_TIME") + ")" +
								" values " +
								"(" + DepartmentQueryStr().replaceAll("d.", "sd.") + ")";
					} else if (queryCommond.getQueryType().equals(SysDepartmentUserBean.QUERY_TYPE)) {
						sql = "merge into base_department_user odu using (select " + DepartmentUserQueryStr() + querySql +
								") sdu on (odu.depuser_id = sdu.depuser_id)" +
								" when matched then update set" +
								" odu.USERID = sdu.USERID," +
								" odu.DEPID = sdu.DEPID," +
								" odu.OLD_DEPTID = sdu.OLD_DEPTID," +
								" odu.ACTIVE_STATE = sdu.ACTIVE_STATE," +
								" odu.ORDER_NUM = sdu.ORDER_NUM," +
								" odu.OPERATE_TIME = sdu.LAST_OPERATE_TIME" +
								" when not matched then insert " +
								"(" + DepartmentUserQueryStr().replaceAll("du.", "odu.").replace("LAST_OPERATE_TIME", "OPERATE_TIME") + ")" +
								" values " +
								"(" + DepartmentUserQueryStr().replaceAll("du.", "sdu.") + ")";
					} else
						return false;
					pstmt = con.prepareStatement(sql);
					pstmt.setString(1, queryCommond.getSearchTreepath() + "%");
					pstmt.execute();
					return true;
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				} finally {
					if (pstmt != null)
						pstmt.close();
				}
			}
		});
	}
	

	/**
	 * 初始化临时用户
	 * @param queryCommond
	 * @return
	 * @author LongJZ
	 * @date 2016-1-13
	 */
	public Boolean cleanTempUser(final SysDeptUserQueryCommond queryCommond) throws Exception {
		return jdbcTemplate.execute(new ConnectionCallback<Boolean>() {
			@Override
			public Boolean doInConnection(Connection con) throws SQLException, DataAccessException {
				PreparedStatement pstmt = null;
				String sql = "";
				try {
					sql = "update base_user set PASSWORD = null, USER_NAME = null, SEX_CODE = null, BIRTHDAY = null, PHONE = null, MOBILE_TELEPHONE = null, " +
							"EMAIL = null, FOLK_CODE = null, PAPER_TYPE_CODE = null, PAPER_NUM = null, POLITICES_CODE = null, DEGREE_CODE = null, DUTY_LEVEL_CODE = null, " +
							"HIERARCHY = null, DUTY_REMARK = null, ZUGONG = null, EXAM_STATE = null, IS_BUREAU_RESERVE = null, CURRENT_DUTY_TIME = null, SHIGUAN = null, " +
							"ACTIVE_STATE = 1, ORDER_NUM = null, AVAILABILITY_STATE = null, OFFICEHOLDING_DATE = null, DANGWU = null, TONGZHAN = null, VIEW_STATE = 1, " +
							"REMARK = null, GUID = null, LEVEL_CODE = null, STUDY_STATE = null, HEADER_PHOTO = null, OPERATE_TIME = null, ENTRY_TIME = null " +
							"where login_id like ?";
					pstmt = con.prepareStatement(sql);
					pstmt.setString(1, queryCommond.getSearchLoginId() + "%");
					pstmt.execute();
					return true;
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				} finally {
					if (pstmt != null)
						pstmt.close();
				}
			}
		});
	}

}
